﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using BCEntity.OA.TeamWork.TeamWorkParticipant;
using BCCommon;
namespace BCData.OA.TeamWork.TeamWorkParticipant
{
    /// <summary>
    /// 协同办公参与者
    /// </summary>
    public class TeamWorkParticipantData : ITeamWorkParticipantData
    {
        private const string QUERY_EXISTS = @"SELECT 1 FROM OA_TeamWorkParticipant";
        private const string QUERY = @"SELECT TeamWorkParticipantId,TeamWorkId,ParticipantId,ParticipantName,CompanyName,DepartmentName,JobTitle 
                                        FROM OA_TeamWorkParticipant";

        private readonly IDatabaseContext databaseContext;

        public TeamWorkParticipantData(IDatabaseContext databaseContext)
        {
            this.databaseContext=databaseContext;
        }

        public async Task<bool> Exists(long teamWorkParticipantId)
        {
            string sql = string.Format(@"{0} WHERE TeamWorkParticipantId=?TeamWorkParticipantId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TeamWorkParticipantId", teamWorkParticipantId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<TeamWorkParticipantEntity> Add(TeamWorkParticipantEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("OA_TeamWorkParticipant", new string[] { "TeamWorkId", "ParticipantId", "ParticipantName", "CompanyName", "DepartmentName", "JobTitle" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TeamWorkId", entity.TeamWorkId, DbType.Int64, size: 20);
            parameters.Add("?ParticipantId", entity.ParticipantId, DbType.Int64, size: 20);
            parameters.Add("?ParticipantName", entity.ParticipantName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CompanyName", entity.CompanyName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?DepartmentName", entity.DepartmentName?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?JobTitle", entity.JobTitle?.Trim(), DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<TeamWorkParticipantEntity>(sql, parameters, databaseContext.Transaction);

        }

        public async Task<bool> Add(List<TeamWorkParticipantEntity> entities)
        {
            List<string> sqls = new List<string>();
            List<DynamicParameters> dynamicParameters = new List<DynamicParameters>();
            string sql = SqlCommands.GetInsertSql("OA_TeamWorkParticipant", new string[] { "TeamWorkId", "ParticipantId", "ParticipantName", "CompanyName", "DepartmentName", "JobTitle" });
            foreach (var entity in entities)
            {
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("?TeamWorkId", entity.TeamWorkId, DbType.Int64, size: 20);
                parameters.Add("?ParticipantId", entity.ParticipantId, DbType.Int64, size: 20);
                parameters.Add("?ParticipantName", entity.ParticipantName?.Trim(), DbType.AnsiString, size: 20);
                parameters.Add("?CompanyName", entity.CompanyName?.Trim(), DbType.AnsiString, size: 50);
                parameters.Add("?DepartmentName", entity.DepartmentName?.Trim(), DbType.AnsiString, size: 100);
                parameters.Add("?JobTitle", entity.JobTitle?.Trim(), DbType.AnsiString, size: 50);
                sqls.Add(sql);
                dynamicParameters.Add(parameters);
            }
            return await this.databaseContext.Master.TransactionExecuteAsync(sqls, dynamicParameters, databaseContext.Transaction) > 0;
        }

        public async Task<TeamWorkParticipantEntity> Update(TeamWorkParticipantEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("OA_TeamWorkParticipant", new string[] { "TeamWorkId", "ParticipantId", "ParticipantName", "CompanyName", "DepartmentName", "JobTitle" },
                new string[] { "TeamWorkParticipantId" });
            sql = string.Format("{0} {1} WHERE TeamWorkParticipantId=?TeamWorkParticipantId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TeamWorkParticipantId", entity.TeamWorkParticipantId, DbType.Int64, size: 20);
            parameters.Add("?TeamWorkId", entity.TeamWorkId, DbType.Int64, size: 20);
            parameters.Add("?ParticipantId", entity.ParticipantId, DbType.Int64, size: 20);
            parameters.Add("?ParticipantName", entity.ParticipantName?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CompanyName", entity.CompanyName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?DepartmentName", entity.DepartmentName?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?JobTitle", entity.JobTitle?.Trim(), DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<TeamWorkParticipantEntity>(sql, parameters, databaseContext.Transaction);
        }

        public async Task<bool> Delete(long teamWorkParticipantId)
        {
            string sql = @"DELETE FROM OA_TeamWorkParticipant WHERE TeamWorkParticipantId=?TeamWorkParticipantId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TeamWorkParticipantId", teamWorkParticipantId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        public async Task<bool> DeleteBatch(long teamWorkId)
        {
            string sql = @"DELETE FROM OA_TeamWorkParticipant WHERE TeamWorkId=?TeamWorkId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TeamWorkId", teamWorkId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task<TeamWorkParticipantEntity> GetEntity(long teamWorkParticipantId)
        {
            string sql = string.Format(@"{0} WHERE TeamWorkParticipantId=?TeamWorkParticipantId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TeamWorkParticipantId", teamWorkParticipantId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<TeamWorkParticipantEntity>(sql, parameters);
        }

        public async Task<IEnumerable<TeamWorkParticipantEntity>> GetList(long teamWorkId)
        {
            string sql = string.Format("{0} WHERE TeamWorkId=?TeamWorkId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TeamWorkId", teamWorkId, DbType.Int64);
            return await this.databaseContext.Slave.QueryAsync<TeamWorkParticipantEntity>(sql, parameters);
        }

        public async Task<(IEnumerable<TeamWorkParticipantEntity>, int)> Query(string participantName, DateTime? start, DateTime? end, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(participantName))
            {
                wheres.Add("ParticipantName=?ParticipantName");
                parameters.Add("?ParticipantName", participantName?.Trim(), DbType.AnsiString);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY CreateTime DESC LIMIT {2},{3}; SELECT COUNT(1) FROM OA_TeamWorkParticipant {1}",
                   QUERY,
                   wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                   (pageIndex - 1) * pageSize,
                   pageSize);
            return await this.databaseContext.Slave.QueryPageableListAsync<TeamWorkParticipantEntity>(sql, parameters);
        }
    }
}
